package dao.impl;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import dao.AdresseDao;
import dao.dto.AdresseDto;
import dao.dto.CodePostalDto;
import dao.dto.ZoneDto;
import boerrors.AdresseInexistantException;

public class AdresseDaoImpl extends AdresseDao {
        private static AdresseDao _instance = null;
        private CallableStatement _callableStatement = null;
        private SQLConnection _connection = new SQLConnection();

        //Constructeur du singleton
        public static AdresseDao getInstance(){
                if(_instance == null) _instance = new AdresseDaoImpl();
                return _instance;
        }

        //Methodes   
        @Override
        public AdresseDto selectId(int idAdresse) throws AdresseInexistantException {
                ResultSet resultat = null;
                ZoneDto zone = null;
                CodePostalDto codePostal = null;
                AdresseDto adresse = null;
                
                try {                                      
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_AdresseSelectId}");
                        //parameter
                        _callableStatement.setInt("idAdresse",idAdresse);
                        //execute
                        resultat = _callableStatement.executeQuery();
                        if (resultat.next()){
                                zone = new ZoneDto( resultat.getInt("idZone"),
                                        resultat.getString("libZone"));
                                codePostal = new CodePostalDto( resultat.getInt("idCodePostal"),
                                                    resultat.getString("cp"),
                                                    resultat.getString("ville"),
                                                    resultat.getString("libPays"),
                                                    zone);
                                adresse = new AdresseDto(   resultat.getInt("idAdresse"),
                                                resultat.getString("rue"),
                                                resultat.getString("numero"),
                                                codePostal
                                                );
                        }
                        return adresse;
                } catch(SQLException e) { 
                        throw new AdresseInexistantException(e);
                } finally { 
                    if (resultat != null) try { resultat.close(); } catch(Exception e) { throw new AdresseInexistantException(e);}
                    if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new AdresseInexistantException(e);}
                    _connection.deconnecter();
                }
        }       
        
    @Override
    public Vector<AdresseDto> listerParClient(int idClient) throws AdresseInexistantException {
        ResultSet resultat = null;
        ZoneDto zone = null;
        CodePostalDto codePostal = null;
        AdresseDto adresse = null;
            try {                                      
                //query
                _callableStatement = _connection.getConnection().prepareCall("{call sp_AdresseListByClient(?)}");    
                //parameter
                _callableStatement.setInt("idClient",idClient);
                //execute
                resultat = _callableStatement.executeQuery();
                
                Vector<AdresseDto> liste = new Vector<AdresseDto>();
                while (resultat.next()) {
                    zone = new ZoneDto( resultat.getInt("idZone"),
                                        resultat.getString("libZone"));
                    codePostal = new CodePostalDto( resultat.getInt("idCodePostal"),
                                                    resultat.getString("cp"),
                                                    resultat.getString("ville"),
                                                    resultat.getString("libPays"),
                                                    zone);
                    adresse = new AdresseDto(   resultat.getInt("idAdresse"),
                                                resultat.getString("rue"),
                                                resultat.getString("numero"),
                                                codePostal
                                                );                    
                    liste.add(adresse);
                }
                return liste;
            } catch(SQLException e) { 
                throw new AdresseInexistantException(e);
            } finally { 
                if (resultat != null) try { resultat.close(); } catch(Exception e) { throw new AdresseInexistantException(e);}
                if (_callableStatement != null) try { _callableStatement.close(); } catch(Exception e) {throw new AdresseInexistantException(e);}
                    _connection.deconnecter();
            }
    }
    
        
/* 
        @Override
        public void enregistrer(AdresseDto adresse) throws AdresseExistantException, Exception {
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_AdresseInsert(?,?,?)}");
                        //parameter                      
                        _callableStatement.setString("rue",adresse.getRue());
                        _callableStatement.setString("numero",adresse.getNumero());
                        _callableStatement.setInt("CodePostalFK",adresse.getCodePostal().getIdCodePostal());
                        //execute
                        _callableStatement.executeUpdate();    
                } catch(SQLException e) {
                        if (e.getMessage().equals("547")) throw new AdresseExistantException("Code postal/Adresse Incorrect",e);
                        else if (e.getMessage().equals("515")) throw new AdresseExistantException("Non autorisé champs vide",e);
                        else  throw new AdresseExistantException(e);
                } catch(Exception e){throw new AdresseExistantException(e);
                } finally { if (_callableStatement != null) 
                            try { _callableStatement.close();
                            } catch(Exception e) {throw e;}
                            _connection.deconnecter();}
            }  

        @Override
        public void modifier(AdresseDto adresse) throws AdresseInexistantException, Exception {
                try {
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_ClientParticulierUpdate(?,?,?)}");
                        //parameter                                               
                        _callableStatement.setString("rue",adresse.getRue());
                        _callableStatement.setString("numero",adresse.getNumero());
                        _callableStatement.setInt("CodePostalFK",adresse.getCodePostal().getIdCodePostal());
                        //execute
                        _callableStatement.executeUpdate();    
                } catch(SQLException e) {
                        if (e.getMessage().equals("547")) {throw new AdresseInexistantException("Code postal/Adresse Incorrect",e);}
                        else if (e.getMessage().equals("515")) {throw new AdresseInexistantException("Non autorisé champs vide",e);}
                        else  throw new AdresseInexistantException(e);                  
                } catch(Exception e){throw new AdresseInexistantException(e);
                } finally {
                        if (_callableStatement != null) 
                        try { _callableStatement.close(); } 
                        catch(Exception e) {throw e;}
                        _connection.deconnecter();}
        }

        @Override
        public Vector<AdresseDto> listeAdressesParticulier(ClientParticulierDto client) throws AdresseInexistantException, Exception {
                ResultSet resultat = null;
                ZoneDto zone = null;
                CodePostalDto codePostal = null;
                AdresseDto adresse = null;
                try {                                      
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_AdressesClientParticulierList}");
                        //parameter
                        _callableStatement.setInt("idClient",client.getIdClient());
                        //execute
                        resultat = _callableStatement.executeQuery();
                        Vector<AdresseDto> liste = new Vector<AdresseDto>();
                        while (resultat.next()) {
                                zone = new ZoneDto(resultat.getInt("idZone"),resultat.getString("libZone"));
                                codePostal = new CodePostalDto( resultat.getInt("idCodePostal"),
                                                                resultat.getString("cp"),
                                                                resultat.getString("ville"),
                                                                resultat.getString("libPays"),
                                                                zone);
                                adresse = new AdresseDto(   resultat.getInt("idAdresse"),
                                                            resultat.getString("rue"),
                                                            resultat.getString("numero"),
                                                            codePostal);
                        }
                        liste.add(adresse);
                        return liste;
                } catch(SQLException e) { throw new AdresseInexistantException(e);
                } finally { 
                        if (resultat != null) 
                                try { resultat.close(); 
                                } catch(Exception e) { throw e;}
                        if (_callableStatement != null) 
                                try { _callableStatement.close(); 
                                } catch(Exception e) {throw e;}
                        _connection.deconnecter();
                }
        }
    
        @Override
        public Vector<AdresseDto> listeAdressesSociete(ClientSocieteDto client) throws AdresseInexistantException, Exception {
                ResultSet resultat = null;
                ZoneDto zone = null;
                CodePostalDto codePostal = null;
                AdresseDto adresse = null;
                try {                                      
                        //query
                        _callableStatement = _connection.getConnection().prepareCall("{call sp_AdressesClientSocieteList}");
                        //parameter
                        _callableStatement.setInt("idClient",client.getIdClient());
                        //execute
                        resultat = _callableStatement.executeQuery();
                        Vector<AdresseDto> liste = new Vector<AdresseDto>();
                        while (resultat.next()) {
                                zone = new ZoneDto(resultat.getInt("idZone"),resultat.getString("libZone"));
                                codePostal = new CodePostalDto( resultat.getInt("idCodePostal"),
                                                                resultat.getString("cp"),
                                                                resultat.getString("ville"),
                                                                resultat.getString("libPays"),
                                                                zone);
                                adresse = new AdresseDto(   resultat.getInt("idAdresse"),
                                                            resultat.getString("rue"),
                                                            resultat.getString("numero"),
                                                            codePostal);
                        }
                        liste.add(adresse);
                        return liste;
                } catch(SQLException e) { throw new AdresseInexistantException(e);
                } finally { 
                        if (resultat != null) 
                                try { resultat.close(); 
                                } catch(Exception e) { throw e;}
                        if (_callableStatement != null) 
                                try { _callableStatement.close(); 
                                } catch(Exception e) {throw e;}
                        _connection.deconnecter();
                }
        }
 */
}